﻿CREATE VIEW vantage.v_Bridge_10
AS
SELECT     Data.AccountNumber AS VantageID, CONVERT(int, LEFT(Data.AccountNumber, 1)) AS CompanyID, RIGHT(Data.AccountNumber, 8) AS UsixID, 
                      Client.LoanClientID, Data.ApplicationNumber, Data.CompanyRef, Data.BranchCodeRef, Data.LoanProgramRef, Data.ProductRef, Data.JurisdictionRef, 
                      Data.PortfolioRef, Data.DealerName, Data.InterestTypeRef, Data.CurrentStatusRef, Data.CurrentStatusDate, Data.CollateralStatusRef, 
                      Data.CollateralStatusDate, Data.OriginalAmountFinanced, Data.AmountFinanced, Data.OpenDate, Data.FirstDueDate, Data.LoanTerm, 
                      Data.OriginalLoanTerm, Data.OriginalInterestRate, Data.InterestRate, Data.OriginalAPR, Data.APR, Data.OriginalDueDay, Data.DueDay, 
                      Data.OriginalPaymentAmount, Data.PaymentAmount, Data.PaymentFrequencyRef, Data.OriginalFinanceCharge, Data.FinanceCharge, 
                      Data.DiscountAmount, Data.OriginalReserve, Data.OriginalWithHold, Data.PurchaseDate, Data.PurchasePrincipalPaid, Data.PurchaseInterest, 
                      Data.PurchaseInterestPaid, Data.LoanFundingDate, Data.NextDueDate, Data.MaturityDate, Data.GrossBalance, Data.InterestAccrued, 
                      Data.InterestPaid, Data.PrinicpalPaid, Data.FeePaid, Data.PastDueInterest, Data.CurrentDueInterest, Data.PastDuePrincipal, Data.CurrentDuePrincipal, 
                      Data.NSFFeeBalance, Data.LateFeeBalance, Data.LegalFeeBalance, Data.PaymentMethodFeeBalance, Data.RepoFeeBalance, 
                      Data.InsuranceFeeBalance, Data.OtherFeeBalance, Data.DefermentFeeBalance, Data.DueDayChangeFeeBalance, Data.CollectionFeeBalance, 
                      Data.RecoveryFeeBalance, Collateral.Make, Collateral.Model, Collateral.ModelYear, Collateral.VIN, Collateral.CashPrice, Collateral.MilageAtPurchase, 
                      Collateral.GPSTrackerID, Collateral.BVAtPurchase, Collateral.Milage, Collateral.MilageAsOfDate, Collateral.BV, Collateral.BVAsOfDate, 
                      Collateral.TitleStateRef, Collateral.IsRegistered, Collateral.IsTitleReceived, Client.RelationshipTypeRef, Client.FirstName, Client.LastName, Client.SSN, 
                      Client.DLN, Client.DOB, Client.FICO, Client.MaritalStatusRef, Client.Occupation, Client.PrimaryLanguageRef, Client.TimeZoneRef, Client.EntityStatusRef,
                       Address.Street, Address.AptSuite, Address.City, Address.CountyRef, Address.StateRef, Address.ZipCode, Data.DPD, Data.PastDueAmount, 
                      P_L.PaymentEffectiveDate AS LastPaymentDate, DATEDIFF(dd, P_L.PaymentEffectiveDate, GETDATE()) AS DSLP, Data.PaymentsMade, 
                      Data.PaymentsPerYear, Data.PrincipalBalance, Data.PerDiem, PHONES.PhoneHome, PHONES.PhoneWork, PHONES.PhoneMobile, 
                      PHONES.PhoneOther
FROM         vantage.v_Bridge_05 AS Data INNER JOIN
                          (SELECT     Target_LoanClientID, LoanClientID, AccountNumber, ClientTypeRef, RelationshipTypeRef, FirstName, MiddleName, MaidenName, 
                                                   LastName, OtherName, SalutationRef, SSN, DLN, DLStateRef, CitizenshipRef, DOB, FICO, MaritalStatusRef, Occupation, 
                                                   PrimaryLanguageRef, TimeZoneRef, EntityStatusRef, Comments
                            FROM          Vantage_In.dbo.Target_LoanClient
                            WHERE      (ClientTypeRef = 'Borrower')) AS Client ON Data.AccountNumber = Client.AccountNumber INNER JOIN
                      Vantage_In.dbo.Target_Collateral AS Collateral ON Data.AccountNumber = Collateral.AccountNumber INNER JOIN
                      Vantage_In.dbo.Target_LoanClientAddress AS Address ON Client.LoanClientID = Address.LoanClientID AND 
                      Client.AccountNumber = Address.AccountNumber INNER JOIN
                      Vantage_In.dbo.t_Address_Current AS A_C ON Address.LoanClientID = A_C.LoanClientID AND 
                      Address.Target_LoanClientAddressID = A_C.Target_LoanClientAddressID LEFT OUTER JOIN
                      Vantage_In.dbo.t_ClientPhones AS PHONES ON Client.LoanClientID = PHONES.LoanClientID LEFT OUTER JOIN
                      Vantage_In.dbo.t_Payment_Last AS P_L ON Data.AccountNumber = P_L.AccountNumber

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[28] 4[31] 2[24] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[50] 4[25] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[26] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Data"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 251
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Client"
            Begin Extent = 
               Top = 198
               Left = 451
               Bottom = 306
               Right = 631
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Collateral"
            Begin Extent = 
               Top = 222
               Left = 38
               Bottom = 330
               Right = 235
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Address"
            Begin Extent = 
               Top = 330
               Left = 38
               Bottom = 438
               Right = 256
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "A_C"
            Begin Extent = 
               Top = 351
               Left = 620
               Bottom = 460
               Right = 780
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PHONES"
            Begin Extent = 
               Top = 184
               Left = 750
               Bottom = 315
               Right = 901
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "P_L"
            Begin Extent = 
               Top = 10
               Left = 841
               Bottom = 88
               Right = 1029
            End
            DisplayFlags = 280', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 111
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 4785
         Alias = 1470
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge_10';

